import logging
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
import openpyxl
import datetime
import os
import sys

import ChengBanBuMen

from openpyxl.cell import MergedCell
timeCur = datetime.datetime.now()
numMonth = 2
# dirPath = 'E:\\日常工作\\11月\\1\\每日必办\\10月考核期'
# dirPath = 'E:\\日常工作\\接诉即办排名情况\\2022\\第三季度\\7月'
dirPath = 'E:\\日常工作\\接诉即办排名情况\\2023\\第一季度\\{}月'.format(numMonth)
# dirPath = 'E:\\日常工作\\11月\\14\\每日必办\\得分统计表\\{}月'.format(numMonth)
# dirPath = os.path.dirname(os.path.realpath(sys.executable))
logFile = dirPath + 'AutoImportExcel.log'
# 创建logger对象
logger = logging.getLogger('test_logger')
# 设置日志等级
logger.setLevel(logging.DEBUG)
# 追加写入文件a ，设置utf-8编码防止中文写入乱码
test_log = logging.FileHandler('test.log', 'a', encoding='utf-8')
# 向文件输出的日志级别
test_log.setLevel(logging.DEBUG)
# 向文件输出的日志信息格式
formatter = logging.Formatter('%(asctime)s [%(levelname)s]  %(filename)s:%(lineno)d:(funcName)s %(message)s')
test_log.setFormatter(formatter)
# 加载文件到logger对象中
logger.addHandler(test_log)
log = logging.getLogger()
stdout_handler = logging.StreamHandler(sys.stdout)
log.addHandler(stdout_handler)

fileJieDan = dirPath + '\\工单明细表-接单.xlsx'
fileKaoHe = dirPath + '\\工单明细表-考核.xlsx'
fileQuanBu = dirPath + '\\全部工单.xlsx'
fileKaoHePiPei = dirPath + '\\工单明细表-考核-已匹配承办部门.xlsx'
fileJieDanPiPei = dirPath + '\\工单明细表-接单-已匹配承办部门.xlsx'
fileJieDanNo = dirPath + '\\工单明细表-接单-未处理.xlsx'
fileChengBanBuMen = dirPath + '\\承办部门.xlsx'
fileResultSave = dirPath + '\\得分统计表.xlsx'


# 打开工单明细表-考核.xlsx
bookKaoHe = openpyxl.load_workbook(fileKaoHe, data_only=True)
sheetKaoHe = bookKaoHe['考核工单']
maxRowKaoHe = sheetKaoHe.max_row
maxColKaoHe = sheetKaoHe.max_column
logger.debug('工单明细表-考核.xlsx -------行数：{}行    列数：{}列'.format(maxRowKaoHe, maxColKaoHe))

# 打开 工单明细表-接单.xlsx
bookJieDan = openpyxl.load_workbook(fileJieDan, data_only=True)
# sheetJieDan = bookJieDan.active
sheetJieDan = bookJieDan['接单情况']
maxRowJieDan = sheetJieDan.max_row
maxColJieDan = sheetJieDan.max_column
logger.debug('工单明细表-接单.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDan, maxColJieDan))


# 获取某一列的索引号 col_name 列名
def get_col_index(sheet_source, index_col, col_name):
    maxColSheetSource = sheet_source.max_column
    for colSource in range(1, maxColSheetSource+1):
        colCellValue = sheet_source.cell(index_col, colSource).value
        if colCellValue is not None:
            colCellValue = colCellValue.strip()
            if colCellValue == col_name:
                return colSource


# 合并 固定的列
def he_bing_cols(sheet_source, index_title, col_name_1, col_name_2, clo_name_out):
    maxRowSource = sheet_source.max_row
    colIndex1 = get_col_index(sheet_source, index_title, col_name_1)
    colIndex2 = get_col_index(sheet_source, index_title, col_name_2)
    colIndexOut = get_col_index(sheet_source, index_title, clo_name_out)
    for colHeBing in range(index_title+1, maxRowSource+1):
        valueColOne = sheet_source.cell(colHeBing, colIndex1).value
        valueColTwo = sheet_source.cell(colHeBing, colIndex2).value
        if valueColOne is not None:
            valueColOne = valueColOne.strip()
            if valueColTwo is not None:
                valueColTwo = valueColTwo.strip()
                cellValueOut = valueColOne + '\n' + valueColTwo
                sheet_source.cell(colHeBing, colIndexOut).value = cellValueOut


def pi_pei_cheng_ban_jie_dan(sheet_src, sheet_quan):
    maxRowSource = sheet_src.max_row
    colGongDanSrc = get_col_index(sheet_src, 1, '工单编号')
    colChengBanSrc = get_col_index(sheet_src, 1, '承办部门')
    colJieDanSrc = get_col_index(sheet_src, 1, '接单部门')
    maxRowQuanBuCur = sheet_quan.max_row
    colGongDanQuan = get_col_index(sheet_quan, 1, '工单编号')
    colChengBanQuan = get_col_index(sheet_quan, 1, '承办部门')
    colJieDanQuan = get_col_index(sheet_quan, 1, '接单部门')
    for rowSrc in range(2, maxRowSource+1):
        valueGongDanSrc = sheet_src.cell(rowSrc, colGongDanSrc).value
        if valueGongDanSrc is not None:
            valueGongDanSrc = valueGongDanSrc.strip()
            for rowQuan in range(2, maxRowQuanBuCur+1):
                valueGongDan = sheet_quan.cell(rowQuan, colGongDanQuan).value
                if valueGongDan is not None:
                    if valueGongDan.find(valueGongDanSrc) != -1:
                        strChengBan = sheet_quan.cell(rowQuan, colChengBanQuan).value
                        strJieDan = sheet_quan.cell(rowQuan, colJieDanQuan).value
                        sheet_src.cell(rowSrc, colChengBanSrc).value = strChengBan
                        sheet_src.cell(rowSrc, colJieDanSrc).value = strJieDan
                        break


# 修改特定的承办部门
def xiu_gai_cheng_ban_bu_men(sheet_source, col_title, col_source, col_dest):
    colTitleIndex = get_col_index(sheet_source, 1, col_title)
    maxRowSource = sheet_source.max_row
    for rowSource in range(2, maxRowSource+1):
        cellValueSearch = sheet_source.cell(rowSource, colTitleIndex).value
        if cellValueSearch is not None:
            cellValueSearch = cellValueSearch.strip()
            isFindSource = cellValueSearch.find(col_source)
            if isFindSource != -1:
                sheet_source.cell(rowSource, colTitleIndex).value = col_dest


# 修改特定的承办部门
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '疑难工单', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '疑难案件', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '应急小分队', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '流动人口管理组', '社会治理组')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '综合城管', '综合行政执法队')

# 修改特定的承办部门
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '疑难工单', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '疑难案件', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '应急小分队', '市民诉求处置中心')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '流动人口管理组', '社会治理组')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '综合城管', '综合行政执法队')

colJieDanInfo = 3                           # 接单情况
numJieDanInfoMerge = 4                           # 接单情况-合并的列数
colCountKaoHe = 7                           # 考核总量

colYiQing = colCountKaoHe + 1                       # 只考核响应率-疫情工单
numYiQingMerge = 4                                  # 只考核响应率-疫情工单 合并的列数
colYiKaoHe = colYiQing                              # 只考核响应率-疫情工单-考核
colYiLian = colYiQing + 1                          # 只考核响应率-疫情工单-联系
colYiLianNo = colYiQing + 2                         # 只考核响应率-疫情工单-未联系
colYiLvLian = colYiQing + 3                       # 只考核响应率-疫情工单-响应率

colOther = colYiLvLian + 1               # 只考核响应率-其他工单
numOtherMerge = 4                                   # 只考核响应率-其他工单 合并的列数
colOtherKaoHe = colOther                            # 只考核响应率-其他工单-考核
colOtherLian = colOtherKaoHe + 1                    # 只考核响应率-其他工单-联系
colOtherLianNo = colOtherKaoHe + 2                  # 只考核响应率-其他工单-未联系
colOtherLvLian = colOtherKaoHe + 3                  # 只考核响应率-其他工单-响应率

colRiChang = colOtherLvLian + 1              # 日常
numRiChangMerge = 11                        # 日常 合并的列数

colRiKaoHe = colRiChang              # 日常-考核
colRiLian = colRiChang + 1              # 日常-联系
colRiLianNo = colRiChang + 2            # 日常-未联系
colRiJie = colRiChang + 3              # 日常-解决
colRiJieNo = colRiChang + 4            # 日常-未解决
colRiMan = colRiChang + 5              # 日常-满意
colRiJiBen = colRiChang + 6            # 日常-基本满意
colRiManNo = colRiChang + 7             # 日常-不满意
colRilvLian = colRiChang + 8              # 日常-响应率
colRiLvJie = colRiChang + 9             # 日常-解决率
colRiLvMan = colRiChang + 10            # 日常-满意率

colAvgLian = colRiLvMan + 1             # 平均响应率
colAvgJie = colAvgLian + 1             # 平均解决率
colAvgMan = colAvgLian + 2             # 平均满意率

colDeFenLian = colAvgMan + 1             # 响应率得分
colDeFenJie = colDeFenLian + 1              # 解决率得分
colDeFenMan = colDeFenLian + 2              # 满意率得分
colDeFenGong = colDeFenLian + 3              # 工单量得分
colDeFenCai = colDeFenLian + 4              # 材料报送得分
colDeFen = colDeFenLian + 5               # 得分总分

colPaiMing = colDeFen + 1               # 排名
colSuoShuPianQu = colDeFen + 2           # 所属片区
colFenDang = colDeFen + 3           # 分档


def init_excel_title(sheet_source, str_title, is_she_qu=0):
    if is_she_qu == 1:
        sheet_source.merge_cells(range_string='A1:AJ1')
    else:
        sheet_source.merge_cells(range_string='A1:AI1')

    sheet_source.cell(1, 1).value = str_title
    align = Alignment(horizontal='center', vertical='center', wrapText=True)
    sheet_source.cell(1, 1).alignment = align
    # 设置行高 第一行 40
    sheet_source.row_dimensions[1].height = 40
    sheet_source.row_dimensions[2].height = 25
    sheet_source.row_dimensions[3].height = 25

    # 序号
    sheet_source.merge_cells(range_string='A2:A3')
    sheet_source.cell(2, 1).value = '序号'
    sheet_source.cell(2, 1).alignment = align
    sheet_source.column_dimensions[get_column_letter(1)].width = 6
    # 接单部门
    sheet_source.merge_cells(range_string='B2:B3')
    sheet_source.cell(2, 2).value = '接单部门'
    sheet_source.cell(2, 2).alignment = align
    sheet_source.column_dimensions[get_column_letter(2)].width = 18
    # 接单总量
    strJieDan = '{}2:{}2'.format(get_column_letter(colJieDanInfo), get_column_letter(colJieDanInfo+numJieDanInfoMerge-1))
    logger.debug('接单情况: {} {}'.format(colJieDanInfo, strJieDan))
    sheet_source.merge_cells(range_string=strJieDan)
    sheet_source.cell(2, colJieDanInfo).value = '接单情况'
    sheet_source.cell(2, colJieDanInfo).alignment = align

    # 接单情况-千人诉求量
    sheet_source.cell(3, colJieDanInfo).value = '千人诉求量'
    sheet_source.cell(3, colJieDanInfo).alignment = align

    # 接单情况-接单总量
    sheet_source.cell(3, colJieDanInfo+1).value = '接单总量'
    sheet_source.cell(3, colJieDanInfo+1).alignment = align

    # 接单情况-疫情
    sheet_source.cell(3, colJieDanInfo+2).value = '疫情'
    sheet_source.cell(3, colJieDanInfo+2).alignment = align

    # # 接单情况-日常
    # sheet_source.cell(3, colJieDanInfo+3).value = '日常'
    # sheet_source.cell(3, colJieDanInfo+3).alignment = align

    # 考核总量
    strNaRuKaoHe = '{}2:{}3'.format(get_column_letter(colCountKaoHe), get_column_letter(colCountKaoHe))
    sheet_source.merge_cells(range_string=strNaRuKaoHe)
    logger.debug('考核总量: {} {}'.format(colCountKaoHe, strNaRuKaoHe))
    sheet_source.cell(2, colCountKaoHe).value = '考核总量'
    sheet_source.cell(2, colCountKaoHe).alignment = align
    # 只考核响应率-疫情工单
    strYiQing = '{}2:{}2'.format(get_column_letter(colYiQing), get_column_letter(colYiQing + numYiQingMerge-1))
    logger.debug('只考核响应率-疫情工单: {} {}'.format(colYiQing, strYiQing))
    sheet_source.merge_cells(range_string=strYiQing)
    sheet_source.cell(2, colYiQing).value = '只考核响应率\r\n疫情工单'
    sheet_source.cell(2, colYiQing).alignment = align
    # 只考核响应率-疫情工单-考核(件)
    sheet_source.cell(3, colYiKaoHe).value = '考核'
    sheet_source.cell(3, colYiKaoHe).alignment = align
    sheet_source.column_dimensions[get_column_letter(colYiKaoHe)].width = 6
    # 只考核响应率-疫情工单-联系(件)
    sheet_source.cell(3, colYiLian).value = '联系'
    sheet_source.cell(3, colYiLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colYiLian)].width = 6
    # 只考核响应率-疫情工单-未联系(件)
    sheet_source.cell(3, colYiLianNo).value = '未联系'
    sheet_source.cell(3, colYiLianNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colYiLianNo)].width = 6
    # 只考核响应率-疫情工单-响应率(件)
    sheet_source.cell(3, colYiLvLian).value = '响应率'
    sheet_source.cell(3, colYiLvLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colYiLvLian)].width = 6

    # 只考核响应率-其他工单
    strYiQing = '{}2:{}2'.format(get_column_letter(colOther), get_column_letter(colOther + numOtherMerge-1))
    logger.debug('只考核响应率-其他工单: {} {}'.format(colOther, strYiQing))
    sheet_source.merge_cells(range_string=strYiQing)
    sheet_source.cell(2, colOther).value = '只考核响应率\r\n其他工单'
    sheet_source.cell(2, colOther).alignment = align
    # 只考核响应率-其他工单-考核(件)
    sheet_source.cell(3, colOtherKaoHe).value = '考核'
    sheet_source.cell(3, colOtherKaoHe).alignment = align
    sheet_source.column_dimensions[get_column_letter(colOtherKaoHe)].width = 6
    # 只考核响应率-其他工单-联系(件)
    sheet_source.cell(3, colOtherLian).value = '联系'
    sheet_source.cell(3, colOtherLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colOtherLian)].width = 6
    # 只考核响应率-其他工单-未联系(件)
    sheet_source.cell(3, colOtherLianNo).value = '未联系'
    sheet_source.cell(3, colOtherLianNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colOtherLianNo)].width = 6
    # 只考核响应率-其他工单-响应率(件)
    sheet_source.cell(3, colOtherLvLian).value = '响应率'
    sheet_source.cell(3, colOtherLvLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colOtherLvLian)].width = 6

    # 日常
    strRiChang = '{}2:{}2'.format(get_column_letter(colRiChang), get_column_letter(colRiChang + numRiChangMerge-1))
    logger.debug('日常工单: {} {}'.format(colRiChang, strRiChang))
    sheet_source.merge_cells(range_string=strRiChang)
    sheet_source.cell(2, colRiChang).value = '考核三率\r\n日常工单'
    sheet_source.cell(2, colRiChang).alignment = align
    # 日常-考核(件)
    sheet_source.cell(3, colRiKaoHe).value = '考核'
    sheet_source.cell(3, colRiKaoHe).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRiKaoHe)].width = 6
    # 日常-联系(件)
    sheet_source.cell(3, colRiLian).value = '联系'
    sheet_source.cell(3, colRiLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRiLian)].width = 6
    # 日常-未联系(件)
    sheet_source.cell(3, colRiLianNo).value = '未联系'
    sheet_source.cell(3, colRiLianNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRiLianNo)].width = 6
    # 日常-解决(件)
    sheet_source.cell(3, colRiJie).value = '解决'
    sheet_source.cell(3, colRiJie).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRiJie)].width = 6
    # 日常-未解决(件)
    sheet_source.cell(3, colRiJieNo).value = '未解决'
    sheet_source.cell(3, colRiJieNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRiJieNo)].width = 6
    # 日常-满意(件)
    sheet_source.cell(3, colRiMan).value = '满意'
    sheet_source.cell(3, colRiMan).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRiMan)].width = 6
    # 日常-基本满意(件)
    sheet_source.cell(3, colRiJiBen).value = '基本满意'
    sheet_source.cell(3, colRiJiBen).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRiJiBen)].width = 9
    # 日常-不满意(件)
    sheet_source.cell(3, colRiManNo).value = '不满意'
    sheet_source.cell(3, colRiManNo).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRiManNo)].width = 6
    # 日常-响应率(件)
    sheet_source.cell(3, colRilvLian).value = '响应率'
    sheet_source.cell(3, colRilvLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRilvLian)].width = 6
    # 日常-解决率(件)
    sheet_source.cell(3, colRiLvJie).value = '解决率'
    sheet_source.cell(3, colRiLvJie).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRiLvJie)].width = 6
    # 日常-满意率(件)
    sheet_source.cell(3, colRiLvMan).value = '满意率'
    sheet_source.cell(3, colRiLvMan).alignment = align
    sheet_source.column_dimensions[get_column_letter(colRiLvMan)].width = 6

    # 平均响应率（%）
    strAvgLian = '{}2:{}3'.format(get_column_letter(colAvgLian), get_column_letter(colAvgLian))
    logger.debug('日常: {} {}'.format(colDeFenLian, strAvgLian))
    sheet_source.merge_cells(range_string=strAvgLian)
    sheet_source.cell(2, colAvgLian).value = '平均\r\n响应率\r\n（%）'
    sheet_source.cell(2, colAvgLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colAvgLian)].width = 9
    # 平均解决率（%）
    strAvgJie = '{}2:{}3'.format(get_column_letter(colAvgJie), get_column_letter(colAvgJie))
    logger.debug('日常: {} {}'.format(colAvgJie, strAvgJie))
    sheet_source.merge_cells(range_string=strAvgJie)
    sheet_source.cell(2, colAvgJie).value = '平均\r\n解决率\r\n（%）'
    sheet_source.cell(2, colAvgJie).alignment = align
    sheet_source.column_dimensions[get_column_letter(colAvgJie)].width = 9
    # 平均满意率（%）
    strAvgMan = '{}2:{}3'.format(get_column_letter(colAvgMan), get_column_letter(colAvgMan))
    logger.debug('日常: {} {}'.format(colAvgMan, strAvgMan))
    sheet_source.merge_cells(range_string=strAvgMan)
    sheet_source.cell(2, colAvgMan).value = '平均\r\n满意率\r\n（%）'
    sheet_source.cell(2, colAvgMan).alignment = align
    sheet_source.column_dimensions[get_column_letter(colAvgMan)].width = 9

    # 响应率得分
    strDeFenLian = '{}2:{}3'.format(get_column_letter(colDeFenLian), get_column_letter(colDeFenLian))
    logger.debug('日常: {} {}'.format(colDeFenLian, strDeFenLian))
    sheet_source.merge_cells(range_string=strDeFenLian)
    sheet_source.cell(2, colDeFenLian).value = '响应率\r\n得分'
    sheet_source.cell(2, colDeFenLian).alignment = align
    sheet_source.column_dimensions[get_column_letter(colDeFenLian)].width = 9
    # 解决率得分
    strDeFenJie = '{}2:{}3'.format(get_column_letter(colDeFenJie), get_column_letter(colDeFenJie))
    logger.debug('日常: {} {}'.format(colDeFenJie, strDeFenJie))
    sheet_source.merge_cells(range_string=strDeFenJie)
    sheet_source.cell(2, colDeFenJie).value = '解决率\r\n得分'
    sheet_source.cell(2, colDeFenJie).alignment = align
    sheet_source.column_dimensions[get_column_letter(colDeFenJie)].width = 9
    # 满意率得分
    strDeFenMan = '{}2:{}3'.format(get_column_letter(colDeFenMan), get_column_letter(colDeFenMan))
    logger.debug('日常: {} {}'.format(colDeFenMan, strDeFenMan))
    sheet_source.merge_cells(range_string=strDeFenMan)
    sheet_source.cell(2, colDeFenMan).value = '满意率\r\n得分'
    sheet_source.cell(2, colDeFenMan).alignment = align
    sheet_source.column_dimensions[get_column_letter(colDeFenMan)].width = 9
    # 工单量得分
    strDeFenGong = '{}2:{}3'.format(get_column_letter(colDeFenGong), get_column_letter(colDeFenGong))
    logger.debug('日常: {} {}'.format(colDeFenGong, strDeFenGong))
    sheet_source.merge_cells(range_string=strDeFenGong)
    sheet_source.cell(2, colDeFenGong).value = '工单量\r\n得分'
    sheet_source.cell(2, colDeFenGong).alignment = align
    sheet_source.column_dimensions[get_column_letter(colDeFenGong)].width = 9
    # 材料报送得分
    strDeFenCai = '{}2:{}3'.format(get_column_letter(colDeFenCai), get_column_letter(colDeFenCai))
    logger.debug('日常: {} {}'.format(colDeFenCai, strDeFenCai))
    sheet_source.merge_cells(range_string=strDeFenCai)
    sheet_source.cell(2, colDeFenCai).value = '材料报\r\n送得分'
    sheet_source.cell(2, colDeFenCai).alignment = align
    sheet_source.column_dimensions[get_column_letter(colDeFenCai)].width = 9
    # 得分
    strDeFen = '{}2:{}3'.format(get_column_letter(colDeFen), get_column_letter(colDeFen))
    logger.debug('日常: {} {}'.format(colDeFen, strDeFen))
    sheet_source.merge_cells(range_string=strDeFen)
    sheet_source.cell(2, colDeFen).value = '得分'
    sheet_source.cell(2, colDeFen).alignment = align
    sheet_source.column_dimensions[get_column_letter(colDeFen)].width = 6
    # 排名
    strPaiMing = '{}2:{}3'.format(get_column_letter(colPaiMing), get_column_letter(colPaiMing))
    logger.debug('日常: {} {}'.format(colPaiMing, strPaiMing))
    sheet_source.merge_cells(range_string=strPaiMing)
    sheet_source.cell(2, colPaiMing).value = '排名'
    sheet_source.cell(2, colPaiMing).alignment = align
    sheet_source.column_dimensions[get_column_letter(colPaiMing)].width = 6

    if is_she_qu == 1:
        # 所属片区
        strSuoShuPianQu = '{}2:{}3'.format(get_column_letter(colSuoShuPianQu), get_column_letter(colSuoShuPianQu))
        logger.debug('日常: {} {}'.format(colSuoShuPianQu, strSuoShuPianQu))
        sheet_source.merge_cells(range_string=strSuoShuPianQu)
        sheet_source.cell(2, colSuoShuPianQu).value = '所属片区'
        sheet_source.cell(2, colSuoShuPianQu).alignment = align
        sheet_source.column_dimensions[get_column_letter(colSuoShuPianQu)].width = 6
        # 分档
        strFenDang = '{}2:{}3'.format(get_column_letter(colFenDang), get_column_letter(colFenDang))
        logger.debug('日常: {} {}'.format(colFenDang, strFenDang))
        sheet_source.merge_cells(range_string=strFenDang)
        sheet_source.cell(2, colFenDang).value = '分档'
        sheet_source.cell(2, colFenDang).alignment = align
        sheet_source.column_dimensions[get_column_letter(colFenDang)].width = 6


sheetKeShi = bookKaoHe.create_sheet(title='科室', index=1)
sheetSheQu = bookKaoHe.create_sheet(title='社区、村', index=1)

logger.debug('承办部门-.xlsx-------')
bookChengBabBuMen = openpyxl.load_workbook(fileChengBanBuMen)
sheetChengBanKeShi = bookChengBabBuMen['科室']
maxRowChengBanKeShi = sheetChengBanKeShi.max_row
maxColChengBanKeShi = sheetChengBanKeShi.max_column
logger.debug('承办部门.xlsx-------科室表 行数：{}行    列数：{}列'.format(maxRowChengBanKeShi, maxColChengBanKeShi))

sheetChengBanSheQu = bookChengBabBuMen['社区村']
maxRowChengBanSheQu = sheetChengBanSheQu.max_row
maxColChengBanSheQu = sheetChengBanSheQu.max_column
logger.debug('承办部门.xlsx-------社区村表 行数：{}行    列数：{}列'.format(maxRowChengBanSheQu, maxColChengBanSheQu))

listKeShi = []
listSheQu = []
listKaoHeYi = {}
listLianXiYi = {}
listWeiLianXiYi = {}
listJieJueYi = {}
listWeiJieJueYi = {}
listManYiYi = {}
listJiBenYi = {}
listBuManYiYi = {}

listKaoHeOther = {}
listLianXiOther = {}
listWeiLianXiOther = {}
lvLianXiOther = {}

listKaoHeRi = {}
listLianXiRi = {}
listWeiLianXiRi = {}
listJieJueRi = {}
listWeiJieJueRi = {}
listManYiRi = {}
listJiBenRi = {}
listBuManYiRi = {}

lvLianXiRi = {}
lvWeiLianXiRi = {}
lvJieJueRi = {}
lvWeiJieJueRi = {}
lvManYiRi = {}
lvJiBenRi = {}
lvBuManYiRi = {}
lvManYiFinalRi = {}
lvLianXiYi = {}
lvWeiLianXiYi = {}
lvJieJueYi = {}
lvWeiJieJueYi = {}
lvManYiYi = {}
lvJiBenYi = {}
lvBuManYiYi = {}
lvManYiFinalYi = {}

lvLianXiAverage = {}
lvWeiLianXiAverage = {}
lvJieJueAverage = {}
lvWeiJieJueAverage = {}
lvManYiAverage = {}
lvJiBenAverage = {}
lvBuManYiAverage = {}
lvManYiFinalAverage = {}

deFenLianXi = {}
deFenManYi = {}
deFenJieJue = {}

listJieDanRi = {}
listJieDanYi = {}

deFenGong = {}
deFenCai = {}
deFenFinal = {}

logger.debug('考核-疫情：{}'.format(listKaoHeYi))
logger.debug('考核-日常：{}'.format(listKaoHeRi))
logger.debug('联系-疫情：{}'.format(listLianXiYi))
logger.debug('联系-日常：{}'.format(listLianXiRi))
logger.debug('未联系-疫情：{}'.format(listWeiLianXiYi))
logger.debug('未联系-日常：{}'.format(listWeiLianXiRi))
logger.debug('解决-疫情：{}'.format(listJieJueYi))
logger.debug('解决-日常：{}'.format(listJieJueRi))
logger.debug('未解决-疫情：{}'.format(listWeiJieJueYi))
logger.debug('未解决-日常：{}'.format(listWeiJieJueRi))
logger.debug('满意-疫情：{}'.format(listManYiYi))
logger.debug('满意-日常：{}'.format(listManYiRi))
logger.debug('基本满意-疫情：{}'.format(listJiBenYi))
logger.debug('基本满意-日常：{}'.format(listJiBenRi))
logger.debug('不满意-疫情：{}'.format(listBuManYiYi))
logger.debug('不满意-日常：{}'.format(listBuManYiRi))

for rowKeshiList in range(2, maxRowChengBanKeShi+1):
    valueCellKeShi = sheetChengBanKeShi.cell(rowKeshiList, 1).value
    if valueCellKeShi is not None:
        listKeShi.append(valueCellKeShi)
        listKaoHeYi[valueCellKeShi] = 0
        listKaoHeRi[valueCellKeShi] = 0
        listLianXiYi[valueCellKeShi] = 0
        listLianXiRi[valueCellKeShi] = 0
        listJieJueYi[valueCellKeShi] = 0
        listJieJueRi[valueCellKeShi] = 0
        listManYiYi[valueCellKeShi] = 0
        listManYiRi[valueCellKeShi] = 0
        listJiBenYi[valueCellKeShi] = 0
        listJiBenRi[valueCellKeShi] = 0
        listWeiLianXiYi[valueCellKeShi] = 0
        listWeiLianXiRi[valueCellKeShi] = 0
        listWeiJieJueYi[valueCellKeShi] = 0
        listWeiJieJueRi[valueCellKeShi] = 0
        listBuManYiYi[valueCellKeShi] = 0
        listBuManYiRi[valueCellKeShi] = 0
        listJieDanRi[valueCellKeShi] = 0
        listJieDanYi[valueCellKeShi] = 0
        deFenLianXi[valueCellKeShi] = 0
        deFenManYi[valueCellKeShi] = 0
        deFenJieJue[valueCellKeShi] = 0
        deFenGong[valueCellKeShi] = 0
        deFenCai[valueCellKeShi] = 0
        listKaoHeOther[valueCellKeShi] = 0
        listLianXiOther[valueCellKeShi] = 0
        listWeiLianXiOther[valueCellKeShi] = 0
        lvLianXiOther[valueCellKeShi] = None
        lvLianXiRi[valueCellKeShi] = None
        lvWeiLianXiRi[valueCellKeShi] = None
        lvJieJueRi[valueCellKeShi] = None
        lvWeiJieJueRi[valueCellKeShi] = None
        lvManYiRi[valueCellKeShi] = None
        lvJiBenRi[valueCellKeShi] = None
        lvBuManYiRi[valueCellKeShi] = None
        lvManYiFinalRi[valueCellKeShi] = None
        lvManYiFinalRi[valueCellKeShi] = None
        lvLianXiYi[valueCellKeShi] = None
        lvWeiLianXiYi[valueCellKeShi] = None
        lvJieJueYi[valueCellKeShi] = None
        lvWeiJieJueYi[valueCellKeShi] = None
        lvManYiYi[valueCellKeShi] = None
        lvJiBenYi[valueCellKeShi] = None
        lvBuManYiYi[valueCellKeShi] = None
        lvManYiFinalYi[valueCellKeShi] = None
        lvLianXiAverage[valueCellKeShi] = None
        lvWeiLianXiAverage[valueCellKeShi] = None
        lvJieJueAverage[valueCellKeShi] = None
        lvWeiJieJueAverage[valueCellKeShi] = None
        lvManYiAverage[valueCellKeShi] = None
        lvJiBenAverage[valueCellKeShi] = None
        lvBuManYiAverage[valueCellKeShi] = None
        lvManYiFinalAverage[valueCellKeShi] = None
        deFenFinal[valueCellKeShi] = None


# 社区村的分档情况
listFenDang = {}
# 社区村的所属片区
listSuoShuPianQu = {}
# 社区村的千人诉求量
listQianRenSuQiu = {}

colChengBanBuMen = get_col_index(sheetChengBanSheQu, 1, '承办部门')
colChengBanBuMenFenDang = get_col_index(sheetChengBanSheQu, 1, '分档')
colChengBanBuMenPianQu = get_col_index(sheetChengBanSheQu, 1, '所属片区')
colChengBanQianRenSuQiu = get_col_index(sheetChengBanSheQu, 1, '千人诉求量')
logger.debug('承办部门：{}   分档：{}   所属片区：{}   千人诉求量：{}   '.format(colChengBanBuMen, colChengBanBuMenFenDang
                                                                             , colChengBanBuMenPianQu
                                                                             , colChengBanQianRenSuQiu))
for rowSheQuList in range(2, maxRowChengBanSheQu+1):
    valueCellSheQu = sheetChengBanSheQu.cell(rowSheQuList, colChengBanBuMen).value
    if valueCellSheQu is not None:
        listSheQu.append(valueCellSheQu)
        listKaoHeYi[valueCellSheQu] = 0
        listKaoHeRi[valueCellSheQu] = 0
        listLianXiYi[valueCellSheQu] = 0
        listLianXiRi[valueCellSheQu] = 0
        listJieJueYi[valueCellSheQu] = 0
        listJieJueRi[valueCellSheQu] = 0
        listManYiYi[valueCellSheQu] = 0
        listManYiRi[valueCellSheQu] = 0
        listJiBenYi[valueCellSheQu] = 0
        listJiBenRi[valueCellSheQu] = 0
        listWeiLianXiYi[valueCellSheQu] = 0
        listWeiLianXiRi[valueCellSheQu] = 0
        listWeiJieJueYi[valueCellSheQu] = 0
        listWeiJieJueRi[valueCellSheQu] = 0
        listBuManYiYi[valueCellSheQu] = 0
        listBuManYiRi[valueCellSheQu] = 0
        listJieDanRi[valueCellSheQu] = 0
        listJieDanYi[valueCellSheQu] = 0
        deFenLianXi[valueCellSheQu] = 0
        deFenManYi[valueCellSheQu] = 0
        deFenJieJue[valueCellSheQu] = 0
        deFenGong[valueCellSheQu] = 0
        deFenCai[valueCellSheQu] = 0
        listKaoHeOther[valueCellSheQu] = 0
        listLianXiOther[valueCellSheQu] = 0
        listWeiLianXiOther[valueCellSheQu] = 0
        lvLianXiOther[valueCellSheQu] = None
        lvLianXiRi[valueCellSheQu] = None
        lvWeiLianXiRi[valueCellSheQu] = None
        lvJieJueRi[valueCellSheQu] = None
        lvWeiJieJueRi[valueCellSheQu] = None
        lvManYiRi[valueCellSheQu] = None
        lvJiBenRi[valueCellSheQu] = None
        lvBuManYiRi[valueCellSheQu] = None
        lvManYiFinalRi[valueCellSheQu] = None
        lvLianXiYi[valueCellSheQu] = None
        lvWeiLianXiYi[valueCellSheQu] = None
        lvJieJueYi[valueCellSheQu] = None
        lvWeiJieJueYi[valueCellSheQu] = None
        lvManYiYi[valueCellSheQu] = None
        lvJiBenYi[valueCellSheQu] = None
        lvBuManYiYi[valueCellSheQu] = None
        lvManYiFinalYi[valueCellSheQu] = None
        lvLianXiAverage[valueCellSheQu] = None
        lvWeiLianXiAverage[valueCellSheQu] = None
        lvJieJueAverage[valueCellSheQu] = None
        lvWeiJieJueAverage[valueCellSheQu] = None
        lvManYiAverage[valueCellSheQu] = None
        lvJiBenAverage[valueCellSheQu] = None
        lvBuManYiAverage[valueCellSheQu] = None
        lvManYiFinalAverage[valueCellSheQu] = None
        deFenFinal[valueCellSheQu] = None
        listSuoShuPianQu[valueCellSheQu] = sheetChengBanSheQu.cell(rowSheQuList, colChengBanBuMenPianQu).value
        listFenDang[valueCellSheQu] = sheetChengBanSheQu.cell(rowSheQuList, colChengBanBuMenFenDang).value
        listQianRenSuQiu[valueCellSheQu] = sheetChengBanSheQu.cell(rowSheQuList, colChengBanQianRenSuQiu).value


logger.debug('承办部门-科室：{}'.format(listKeShi))
logger.debug('承办部门-社区/村：{}'.format(listSheQu))

sheetJieDan.insert_cols(1, 1)
sheetJieDan.cell(1, 1).value = '是否已处理'

colChengBanJieDan = get_col_index(sheetJieDan, 1, '承办部门')
colLeiXingJieDan = get_col_index(sheetJieDan, 1, '标签')

maxRowJieDan = sheetJieDan.max_row
logger.debug('maxRowJieDan ：{}'.format(maxRowJieDan))
# lineIndexJieDan = 2
numWeiChuLi = 0
# while lineIndexJieDan <= maxRowJieDan:
for lineIndexJieDan in range(2, maxRowJieDan+1):
    valueChengBanJieDan = sheetJieDan.cell(lineIndexJieDan, colChengBanJieDan).value
    valueLeiXingJieDan = sheetJieDan.cell(lineIndexJieDan, colLeiXingJieDan).value
    logger.debug('接单 {} {} {}'.format(lineIndexJieDan, valueChengBanJieDan, valueLeiXingJieDan))
    if valueChengBanJieDan is not None and valueLeiXingJieDan is not None:
        valueChengBanJieDan = valueChengBanJieDan.strip()
        strLeiXing = '{}'.format(valueLeiXingJieDan)
        if valueChengBanJieDan in listKeShi or valueChengBanJieDan in listSheQu:
            if strLeiXing.find('1') != -1:
                logger.debug('接单  {} {} 疫情'.format(valueChengBanJieDan, valueLeiXingJieDan))
                listJieDanYi[valueChengBanJieDan] = listJieDanYi[valueChengBanJieDan] + 1
                # sheetJieDan.delete_rows(lineIndexJieDan)
                # maxRowJieDan = maxRowJieDan - 1
                sheetJieDan.cell(lineIndexJieDan, 1).value = '已处理-疫情'
            else:
                logger.debug('接单  {} {} 日常'.format(valueChengBanJieDan, valueLeiXingJieDan))
                listJieDanRi[valueChengBanJieDan] = listJieDanRi[valueChengBanJieDan] + 1
                # sheetJieDan.delete_rows(lineIndexJieDan)
                # maxRowJieDan = maxRowJieDan - 1
                sheetJieDan.cell(lineIndexJieDan, 1).value = '已处理-日常'
        else:
            logger.debug('接单  {} {} 不在承办部门列表'.format(valueChengBanJieDan, valueLeiXingJieDan))
            # lineIndexJieDan = lineIndexJieDan + 1
            sheetJieDan.cell(lineIndexJieDan, 1).value = '未找到该承办部门'
            numWeiChuLi = numWeiChuLi + 1
    else:
        logger.debug('接单  {} {} 没有承办部门或标签类型'.format(valueChengBanJieDan, valueLeiXingJieDan))
        # lineIndexJieDan = lineIndexJieDan + 1
        sheetJieDan.cell(lineIndexJieDan, 1).value = '没有承办部门或标签类型'
        numWeiChuLi = numWeiChuLi + 1

logger.debug('接单量-疫情：{}'.format(listJieDanYi))
logger.debug('接单量-日常：{}'.format(listJieDanRi))
# maxRowJieDan2 = sheetJieDan.max_row
if numWeiChuLi > 2:
    # 保存未匹配承办部门的表格
    bookJieDan.save(fileJieDanNo)
    logger.debug('保存  {}'.format(fileJieDanNo))

colChengBanIndex = get_col_index(sheetKaoHe, 1, '承办部门')
colLeiXing = get_col_index(sheetKaoHe, 1, '考核标签')
colLianXi = get_col_index(sheetKaoHe, 1, '是否联系')
colJieJue = get_col_index(sheetKaoHe, 1, '是否解决')
colManYi = get_col_index(sheetKaoHe, 1, '是否满意')
logger.debug('承办部门：{}列  '.format(colChengBanIndex))
logger.debug('标签：{}列'.format(colLeiXing))
logger.debug('联系：{}列'.format(colLianXi))
logger.debug('解决：{}列'.format(colJieJue))
logger.debug('满意：{}列'.format(colManYi))
lineIndex = 2
numKaoHe = 1
maxRowKaoHeShow = maxRowKaoHe
while lineIndex <= maxRowKaoHe:
    valueChengBanCur = sheetKaoHe.cell(lineIndex, colChengBanIndex).value
    valueLeiXing = sheetKaoHe.cell(lineIndex, colLeiXing).value
    valueLianxi = sheetKaoHe.cell(lineIndex, colLianXi).value
    valueJieJue = sheetKaoHe.cell(lineIndex, colJieJue).value
    valueManYi = sheetKaoHe.cell(lineIndex, colManYi).value
    logger.debug('考核 {}/{} {} {} {} {} {}'.format(numKaoHe, maxRowKaoHeShow, valueChengBanCur, valueLeiXing
                                                    , valueLianxi, valueJieJue, valueManYi))
    numKaoHe = numKaoHe + 1
    if valueChengBanCur is not None and valueLeiXing is not None:
        valueChengBanCur = valueChengBanCur.strip()
        strLeiXing = '{}'.format(valueLeiXing)
        if valueChengBanCur in listKeShi or valueChengBanCur in listSheQu:
            if strLeiXing.find('1') == -1:    # 日常工单
                logger.debug(
                    '考核 {}/{} {} {} {} {} {} 考核三率'.format(numKaoHe, maxRowKaoHeShow, valueChengBanCur, valueLeiXing
                                                       , valueLianxi, valueJieJue, valueManYi))
                if valueLianxi is not None:
                    valueLianxi = valueLianxi.strip()
                if valueJieJue is not None:
                    valueJieJue = valueJieJue.strip()
                if valueManYi is not None:
                    valueManYi = valueManYi.strip()
                listKaoHeRi[valueChengBanCur] = listKaoHeRi[valueChengBanCur] + 1
                if valueLianxi == '联系' or valueLianxi == '已联系' or valueLianxi == '是':
                    listLianXiRi[valueChengBanCur] = listLianXiRi[valueChengBanCur] + 1
                if valueLianxi == '未联系' or valueLianxi == '否':
                    listWeiLianXiRi[valueChengBanCur] = listWeiLianXiRi[valueChengBanCur] + 1
                if valueJieJue == '解决' or valueJieJue == '已解决' or valueJieJue == '是':
                    listJieJueRi[valueChengBanCur] = listJieJueRi[valueChengBanCur] + 1
                if valueJieJue == '未解决' or valueJieJue == '否':
                    listWeiJieJueRi[valueChengBanCur] = listWeiJieJueRi[valueChengBanCur] + 1
                if valueManYi == '满意' or valueManYi == '非常满意':
                    listManYiRi[valueChengBanCur] = listManYiRi[valueChengBanCur] + 1
                if valueManYi == '基本满意':
                    listJiBenRi[valueChengBanCur] = listJiBenRi[valueChengBanCur] + 1
                if valueManYi == '不满意' or valueManYi == '非常不满意':
                    listBuManYiRi[valueChengBanCur] = listBuManYiRi[valueChengBanCur] + 1
                sheetKaoHe.delete_rows(lineIndex)
                maxRowQuanBu = maxRowKaoHe - 1
            elif strLeiXing == '1001':
                logger.debug('考核 {}/{} {} {} {} {} {} '
                    '仅考核响应率-其他'.format(numKaoHe, maxRowKaoHeShow, valueChengBanCur, valueLeiXing
                                                       , valueLianxi, valueJieJue, valueManYi))
                listKaoHeOther[valueChengBanCur] = listKaoHeOther[valueChengBanCur] + 1
                if valueLianxi == '联系' or valueLianxi == '已联系' or valueLianxi == '是':
                    listLianXiOther[valueChengBanCur] = listLianXiOther[valueChengBanCur] + 1
                if valueLianxi == '未联系' or valueLianxi == '否':
                    listWeiLianXiOther[valueChengBanCur] = listWeiLianXiOther[valueChengBanCur] + 1
                sheetKaoHe.delete_rows(lineIndex)
                maxRowQuanBu = maxRowKaoHe - 1
            else:
                logger.debug('考核 {}/{} {} {} {} {} {} '
                             '仅考核响应率-疫情'.format(numKaoHe, maxRowKaoHeShow, valueChengBanCur, valueLeiXing
                                                        , valueLianxi, valueJieJue, valueManYi))
                listKaoHeYi[valueChengBanCur] = listKaoHeYi[valueChengBanCur] + 1
                if valueLianxi == '联系' or valueLianxi == '已联系' or valueLianxi == '是':
                    listLianXiYi[valueChengBanCur] = listLianXiYi[valueChengBanCur] + 1
                if valueLianxi == '未联系' or valueLianxi == '否':
                    listWeiLianXiYi[valueChengBanCur] = listWeiLianXiYi[valueChengBanCur] + 1
                sheetKaoHe.delete_rows(lineIndex)
                maxRowQuanBu = maxRowKaoHe - 1
            logger.debug('考核 {}/{} {} '
                         '考核三率：{} '
                         '响应率-其他：{} '
                         '响应率-疫情：{}'.format(numKaoHe, maxRowKaoHeShow, valueChengBanCur
                                                       , listKaoHeRi[valueChengBanCur], listKaoHeOther[valueChengBanCur]
                                                       , listKaoHeYi[valueChengBanCur]))
        else:
            lineIndex = lineIndex + 1
    else:
        lineIndex = lineIndex + 1

logger.debug('考核-疫情：{}'.format(listKaoHeYi))
logger.debug('考核-日常：{}'.format(listKaoHeRi))
logger.debug('联系-疫情：{}'.format(listLianXiYi))
logger.debug('联系-日常：{}'.format(listLianXiRi))
logger.debug('未联系-疫情：{}'.format(listWeiLianXiYi))
logger.debug('未联系-日常：{}'.format(listWeiLianXiRi))
logger.debug('解决-疫情：{}'.format(listJieJueYi))
logger.debug('解决-日常：{}'.format(listJieJueRi))
logger.debug('未解决-疫情：{}'.format(listWeiJieJueYi))
logger.debug('未解决-日常：{}'.format(listWeiJieJueRi))
logger.debug('满意-疫情：{}'.format(listManYiYi))
logger.debug('满意-日常：{}'.format(listManYiRi))
logger.debug('基本满意-疫情：{}'.format(listJiBenYi))
logger.debug('基本满意-日常：{}'.format(listJiBenRi))
logger.debug('不满意-疫情：{}'.format(listBuManYiYi))
logger.debug('不满意-日常：{}'.format(listBuManYiRi))

countJieDanRi = 0
countJieDanYi = 0
countKaoHeRi = 0
countKaoHeYi = 0
countKaoHeOther = 0
countLianXiRi = 0
countLianXiYi = 0
countLianXiOther = 0
countWeiLianXiRi = 0
countWeiLianXiYi = 0
countWeiLianXiOther = 0
countJieJueRi = 0
countJieJueYi = 0
countWeiJieJueRi = 0
countWeiJieJueYi = 0
countManYiRi = 0
countManYiYi = 0
countJiBenRi = 0
countJiBenYi = 0
countBuManYiRi = 0
countBuManYiYi = 0
countKaoHeRiKeShi = 0
countKaoHeYiKeShi = 0
countKaoHeOtherKeShi = 0
countKaoHeRiSheQu = 0
countKaoHeYiSheQu = 0
countKaoHeOtherSheQu = 0

for indexTongJi in range(0, len(listKeShi)):
    nameTongJi = listKeShi[indexTongJi]
    countJieDanRi = countJieDanRi + listJieDanRi[nameTongJi]
    countJieDanYi = countJieDanYi + listJieDanYi[nameTongJi]
    countKaoHeRi = countKaoHeRi + listKaoHeRi[nameTongJi]
    countKaoHeRiKeShi = countKaoHeRiKeShi + listKaoHeRi[nameTongJi]
    countKaoHeYi = countKaoHeYi + listKaoHeYi[nameTongJi]
    countKaoHeYiKeShi = countKaoHeYiKeShi + listKaoHeYi[nameTongJi]
    countLianXiRi = countLianXiRi + listLianXiRi[nameTongJi]
    countLianXiYi = countLianXiYi + listLianXiYi[nameTongJi]
    countWeiLianXiRi = countWeiLianXiRi + listWeiLianXiRi[nameTongJi]
    countWeiLianXiYi = countWeiLianXiYi + listWeiLianXiYi[nameTongJi]
    countJieJueRi = countJieJueRi + listJieJueRi[nameTongJi]
    countJieJueYi = countJieJueYi + listJieJueYi[nameTongJi]
    countWeiJieJueRi = countWeiJieJueRi + listWeiJieJueRi[nameTongJi]
    countWeiJieJueYi = countWeiJieJueYi + listWeiJieJueYi[nameTongJi]
    countManYiRi = countManYiRi + listManYiRi[nameTongJi]
    countManYiYi = countManYiYi + listManYiYi[nameTongJi]
    countJiBenRi = countJiBenRi + listJiBenRi[nameTongJi]
    countJiBenYi = countJiBenYi + listJiBenYi[nameTongJi]
    countBuManYiRi = countBuManYiRi + listBuManYiRi[nameTongJi]
    countBuManYiYi = countBuManYiYi + listBuManYiYi[nameTongJi]
    countKaoHeOther = countKaoHeOther + listKaoHeOther[nameTongJi]
    countLianXiOther = countLianXiOther + listLianXiOther[nameTongJi]
    countWeiLianXiOther = countWeiLianXiOther + listWeiLianXiOther[nameTongJi]
    countKaoHeOtherKeShi = countKaoHeOtherKeShi + listKaoHeOther[nameTongJi]


for indexTongJi in range(0, len(listSheQu)):
    nameTongJi = listSheQu[indexTongJi]
    countJieDanRi = countJieDanRi + listJieDanRi[nameTongJi]
    countJieDanYi = countJieDanYi + listJieDanYi[nameTongJi]
    countKaoHeRi = countKaoHeRi + listKaoHeRi[nameTongJi]
    countKaoHeRiSheQu = countKaoHeRiSheQu + listKaoHeRi[nameTongJi]
    countKaoHeYi = countKaoHeYi + listKaoHeYi[nameTongJi]
    countKaoHeYiSheQu = countKaoHeYiSheQu + listKaoHeYi[nameTongJi]
    countLianXiRi = countLianXiRi + listLianXiRi[nameTongJi]
    countLianXiYi = countLianXiYi + listLianXiYi[nameTongJi]
    countWeiLianXiRi = countWeiLianXiRi + listWeiLianXiRi[nameTongJi]
    countWeiLianXiYi = countWeiLianXiYi + listWeiLianXiYi[nameTongJi]
    countJieJueRi = countJieJueRi + listJieJueRi[nameTongJi]
    countJieJueYi = countJieJueYi + listJieJueYi[nameTongJi]
    countWeiJieJueRi = countWeiJieJueRi + listWeiJieJueRi[nameTongJi]
    countWeiJieJueYi = countWeiJieJueYi + listWeiJieJueYi[nameTongJi]
    countManYiRi = countManYiRi + listManYiRi[nameTongJi]
    countManYiYi = countManYiYi + listManYiYi[nameTongJi]
    countJiBenRi = countJiBenRi + listJiBenRi[nameTongJi]
    countJiBenYi = countJiBenYi + listJiBenYi[nameTongJi]
    countBuManYiRi = countBuManYiRi + listBuManYiRi[nameTongJi]
    countBuManYiYi = countBuManYiYi + listBuManYiYi[nameTongJi]
    countKaoHeOther = countKaoHeOther + listKaoHeOther[nameTongJi]
    countLianXiOther = countLianXiOther + listLianXiOther[nameTongJi]
    countWeiLianXiOther = countWeiLianXiOther + listWeiLianXiOther[nameTongJi]
    countKaoHeOtherSheQu = countKaoHeOtherSheQu + listKaoHeOther[nameTongJi]

logger.debug('只考核响应率-疫情工单-考核：{}'.format(countKaoHeYi))
logger.debug('只考核响应率-疫情工单-联系：{}'.format(countLianXiYi))
logger.debug('只考核响应率-疫情工单-未联系：{}'.format(countWeiLianXiYi))

logger.debug('只考核响应率-其他工单-考核：{}'.format(countKaoHeOther))
logger.debug('只考核响应率-其他工单-联系：{}'.format(countLianXiOther))
logger.debug('只考核响应率-其他工单-未联系：{}'.format(countWeiLianXiOther))

logger.debug('考核-日常：{}'.format(countKaoHeRi))
logger.debug('联系-日常：{}'.format(countLianXiRi))
logger.debug('未联系-日常：{}'.format(countWeiLianXiRi))
logger.debug('解决-日常：{}'.format(countJieJueRi))
logger.debug('未解决-日常：{}'.format(countWeiJieJueRi))
logger.debug('满意-日常：{}'.format(countManYiRi))
logger.debug('基本满意-日常：{}'.format(countJiBenRi))
logger.debug('不满意-日常：{}'.format(countBuManYiRi))


def make_lv_ping_jun(lv_ri_chang, lv_yi_qing, name_bu_men, lv_list, zhi_only, lv_other=None):
    if zhi_only:
        lv_list[name_bu_men] = lv_ri_chang
    else:
        numCount = 0
        sumCount = 0
        if lv_yi_qing is not None:
            sumCount = sumCount + lv_yi_qing
            numCount = numCount + 1
        if lv_ri_chang is not None:
            sumCount = sumCount + lv_ri_chang
            numCount = numCount + 1
        if lv_other is not None:
            sumCount = sumCount + lv_other
            numCount = numCount + 1
        if sumCount == 1:
            lv_list[name_bu_men] = sumCount
        elif sumCount > 0:
            lv_list[name_bu_men] = round(sumCount / numCount, 2)


def make_de_fen_info(name_bu_men):
    kaoHeCount = listKaoHeRi[name_bu_men] + listKaoHeYi[name_bu_men] + listKaoHeOther[name_bu_men]
    if kaoHeCount != 0:
        if listLianXiRi[name_bu_men] + listLianXiYi[name_bu_men] + listLianXiOther[name_bu_men] == kaoHeCount:
            deFenLianXi[name_bu_men] = 5
        else:
            deFenLianXi[name_bu_men] = 0
        if listKaoHeRi[name_bu_men] == 0:
            deFenJieJue[name_bu_men] = 20
            deFenManYi[name_bu_men] = 15
        else:
            deFenJieJue[name_bu_men] = round(listJieJueRi[name_bu_men] / listKaoHeRi[name_bu_men] * 20, 2)
            deFenManYi[name_bu_men] = round((listManYiRi[name_bu_men]
                                             + listJiBenRi[name_bu_men])/listKaoHeRi[name_bu_men] * 15, 2)
    else:
        deFenLianXi[name_bu_men] = 5
        deFenJieJue[name_bu_men] = 20
        deFenManYi[name_bu_men] = 15
    deFenFinal[name_bu_men] = deFenLianXi[name_bu_men] + deFenJieJue[name_bu_men] + deFenManYi[name_bu_men]
    if name_bu_men in listSheQu:
        jieDanCount = listJieDanRi[name_bu_men] + listJieDanYi[name_bu_men]
        if jieDanCount == 0:
            deFenGong[name_bu_men] = 10
        else:
            deFenGong[name_bu_men] = 5 - (jieDanCount - listQianRenSuQiu[name_bu_men]) * 0.2
        if deFenGong[name_bu_men] < 0:
            deFenGong[name_bu_men] = 0
        if deFenGong[name_bu_men] > 10:
            deFenGong[name_bu_men] = 10
        deFenFinal[name_bu_men] = deFenFinal[name_bu_men] + deFenGong[name_bu_men]


def make_pai_ming_shu_ju(list_src):
    for rowIndex in range(0, len(list_src)):
        buMenName = list_src[rowIndex]
        if listKaoHeRi[buMenName] != 0:
            lvLianXiRi[buMenName] = round(listLianXiRi[buMenName]/listKaoHeRi[buMenName]*100, 2)
            lvWeiLianXiRi[buMenName] = round(listWeiLianXiRi[buMenName]/listKaoHeRi[buMenName]*100, 2)
            lvJieJueRi[buMenName] = round(listJieJueRi[buMenName] / listKaoHeRi[buMenName]*100, 2)
            lvWeiJieJueRi[buMenName] = round(listWeiJieJueRi[buMenName] / listKaoHeRi[buMenName] * 100, 2)
            lvManYiRi[buMenName] = round(listManYiRi[buMenName] / listKaoHeRi[buMenName] * 100, 2)
            lvJiBenRi[buMenName] = round(listJiBenRi[buMenName] / listKaoHeRi[buMenName]*100, 2)
            lvBuManYiRi[buMenName] = round(listBuManYiRi[buMenName] / listKaoHeRi[buMenName] * 100, 2)
            lvManYiFinalRi[buMenName] = round((listManYiRi[buMenName] + listJiBenRi[buMenName])
                                              / listKaoHeRi[buMenName] * 100, 2)
        if listKaoHeYi[buMenName] != 0:
            lvLianXiYi[buMenName] = round(listLianXiYi[buMenName]/listKaoHeYi[buMenName]*100, 2)
        if listKaoHeOther[buMenName] != 0:
            lvLianXiOther[buMenName] = round(listLianXiOther[buMenName]/listKaoHeOther[buMenName]*100, 2)

        make_lv_ping_jun(lvLianXiRi[buMenName], lvLianXiYi[buMenName], buMenName
                         , lvLianXiAverage, False, lvLianXiOther[buMenName])
        make_lv_ping_jun(lvJieJueRi[buMenName], lvJieJueYi[buMenName], buMenName, lvJieJueAverage, True)
        make_lv_ping_jun(lvManYiRi[buMenName], lvManYiYi[buMenName], buMenName, lvManYiAverage, True)
        make_lv_ping_jun(lvJiBenRi[buMenName], lvJiBenYi[buMenName], buMenName, lvJiBenAverage, True)
        make_lv_ping_jun(lvBuManYiRi[buMenName], lvBuManYiYi[buMenName], buMenName, lvBuManYiAverage, True)
        make_lv_ping_jun(lvManYiFinalRi[buMenName], lvManYiFinalYi[buMenName], buMenName, lvManYiFinalAverage, True)

        # 联系率计算平均值
        make_de_fen_info(buMenName)

        logger.debug('{} 日常 考核：{}  联系：{} 联系率：{} 未联系：{} 未联系率：{} 解决：{} 解决率：{} 未解决：{} 未解决率：{} '
                     '双满：{} 双满率：{} 基本满意：{} 基本满意率：{} 不满意：{} 不满意率：{} 满意率：{}'
                     .format(buMenName, listKaoHeRi[buMenName], listLianXiRi[buMenName], lvLianXiRi[buMenName]
                             , listWeiLianXiRi[buMenName], lvWeiLianXiRi[buMenName], listJieJueRi[buMenName]
                             , lvJieJueRi[buMenName], listWeiJieJueRi[buMenName], lvWeiJieJueRi[buMenName]
                             , listManYiRi[buMenName], lvManYiRi[buMenName], listJiBenRi[buMenName]
                             , lvJiBenRi[buMenName], listBuManYiRi[buMenName], lvBuManYiRi[buMenName]
                             , lvManYiFinalRi[buMenName]))
        logger.debug('{} 疫情 考核：{}  联系：{} 联系率：{} 未联系：{} 未联系率：{} 解决：{} 解决率：{} 未解决：{} 未解决率：{} '
                     '双满：{} 双满率：{} 基本满意：{} 基本满意率：{} 不满意：{} 不满意率：{} 满意率：{}'
                     .format(buMenName, listKaoHeYi[buMenName], listLianXiYi[buMenName], lvLianXiYi[buMenName]
                             , listWeiLianXiYi[buMenName], lvWeiLianXiYi[buMenName], listJieJueYi[buMenName]
                             , lvJieJueYi[buMenName], listWeiJieJueYi[buMenName], lvWeiJieJueYi[buMenName]
                             , listManYiYi[buMenName], lvManYiYi[buMenName], listJiBenYi[buMenName]
                             , lvJiBenYi[buMenName], listBuManYiYi[buMenName], lvBuManYiYi[buMenName]
                             , lvManYiFinalYi[buMenName]))
        logger.debug('{} 平均  联系率：{} 未联系率：{} 解决率：{} 未解决率：{} 双满率：{} 基本满意率：{} 不满意率：{} 满意率：{}'
                     .format(buMenName, lvLianXiAverage[buMenName], lvWeiLianXiAverage[buMenName]
                             , lvJieJueAverage[buMenName], lvWeiJieJueAverage[buMenName], lvManYiAverage[buMenName]
                             , lvJiBenAverage[buMenName], lvBuManYiAverage[buMenName], lvManYiFinalAverage[buMenName]))


make_pai_ming_shu_ju(listKeShi)
make_pai_ming_shu_ju(listSheQu)

# '拱辰街道科室诉求总量反映情况统计（2022年10月19日0时至10月31日9时）'
strTitleKeShi = '{}月考核期拱辰街道科室接诉即办排名情况\r\n（'.format(numMonth)
strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(timeCur.year, numMonth-1)
strTitleKeShi = strTitleKeShi + '{}月18日24时)'.format(numMonth)

# 拱辰街道社区、村诉求考核反映情况统计（2022年9月19日0时至10月8日12时）
strTitleSheQu = '{}月考核期拱辰街道社区、村接诉即办排名情况\r\n（'.format(numMonth)
strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(timeCur.year, numMonth-1)
strTitleSheQu = strTitleSheQu + '{}月18日24时)'.format(numMonth)

init_excel_title(sheetKeShi, strTitleKeShi)
init_excel_title(sheetSheQu, strTitleSheQu, 1)

strMain = '考核方法：\r\n1.工单里考核，仅考虑接单量，不考虑考核量，例如：接单量为0，考核量不为0时，算作零诉求社区，加5分'
strMain = strMain + '\r\n2.计算满意率时，基本满意算作满意工单'


def insert_into_sheet(sheet_insert, inset_bu_men, de_fen, is_she_qu=0):
    rowCount = sheet_insert.max_row
    rowInsert = rowCount + 1
    if de_fen is not None:
        for rowPai in range(4, rowCount+1):
            deFenRow = sheet_insert.cell(rowPai, colDeFen).value
            if deFenRow is not None:
                if de_fen >= deFenRow:
                    sheet_insert.insert_rows(rowPai)
                    rowInsert = rowPai
                    break
            else:
                sheet_insert.insert_rows(rowPai)
                rowInsert = rowPai
                break
    sheet_insert.cell(rowInsert, 2).value = inset_bu_men            # 接单部门
    logger.debug('{} 接单量：{}  疫情：{}   '
                 '日常：{} '.format(inset_bu_men, listJieDanRi[inset_bu_men] + listJieDanYi[inset_bu_men]
                                   , listJieDanYi[inset_bu_men], listJieJueRi[inset_bu_men]))
    if listJieDanRi[inset_bu_men] + listJieDanYi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colJieDanInfo+1).value = listJieDanRi[inset_bu_men] + listJieDanYi[inset_bu_men]  # 接单总量
    if listJieDanYi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colJieDanInfo+2).value = listJieDanYi[inset_bu_men]  # 接单总量(件)-疫情
    if listJieDanRi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colJieDanInfo+3).value = listJieDanRi[inset_bu_men]  # 接单总量(件)-日常
    if listKaoHeRi[inset_bu_men] + listKaoHeYi[inset_bu_men] + listKaoHeOther[inset_bu_men] != 0:
        countAll = listKaoHeRi[inset_bu_men] + listKaoHeYi[inset_bu_men] + listKaoHeOther[inset_bu_men]  # 纳入考核(件)-总量
        sheet_insert.cell(rowInsert, colCountKaoHe).value = countAll
    # 只考核响应率-疫情工单
    if listKaoHeYi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colYiKaoHe).value = listKaoHeYi[inset_bu_men]  # 只考核响应率-疫情工单-考核
    if listLianXiYi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colYiLian).value = listLianXiYi[inset_bu_men]  # 只考核响应率-疫情工单-联系
    if listWeiLianXiYi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colYiLianNo).value = listWeiLianXiYi[inset_bu_men]  # 只考核响应率-疫情工单-未联系
    if lvLianXiYi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colYiLvLian).value = lvLianXiYi[inset_bu_men]  # 只考核响应率-疫情工单-响应率
    # 只考核响应率-其他工单
    if listKaoHeOther[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colOtherKaoHe).value = listKaoHeOther[inset_bu_men]  # 只考核响应率-其他工单-考核
    if listLianXiOther[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colOtherLian).value = listLianXiOther[inset_bu_men]  # 只考核响应率-其他工单-联系
    if listWeiLianXiOther[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colOtherLianNo).value = listWeiLianXiOther[inset_bu_men]  # 只考核响应率-其他工单-未联系
    if lvLianXiOther[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colOtherLvLian).value = lvLianXiOther[inset_bu_men]  # 只考核响应率-其他工单-响应率

    # 日常工单
    if listKaoHeRi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colRiKaoHe).value = listKaoHeRi[inset_bu_men]  # 日常-考核
    if listLianXiRi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colRiLian).value = listLianXiRi[inset_bu_men]  # 日常-联系
    if listWeiLianXiRi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colRiLianNo).value = listWeiLianXiRi[inset_bu_men]  # 日常-未联系
    if listJieJueRi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colRiJie).value = listJieJueRi[inset_bu_men]  # 日常-解决
    if listWeiJieJueRi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colRiJieNo).value = listWeiJieJueRi[inset_bu_men]  # 日常-未解决
    if listManYiRi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colRiMan).value = listManYiRi[inset_bu_men]  # 日常-满意
    if listJiBenRi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colRiJiBen).value = listJiBenRi[inset_bu_men]  # 日常-基本满意
    if listBuManYiRi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colRiManNo).value = listBuManYiRi[inset_bu_men]  # 日常-不满意
    if lvLianXiRi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colRilvLian).value = lvLianXiRi[inset_bu_men]  # 日常-响应率
    if lvJieJueRi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colRiLvJie).value = lvJieJueRi[inset_bu_men]  # 日常-解决率
    if lvManYiFinalRi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colRiLvMan).value = lvManYiFinalRi[inset_bu_men]  # 日常-满意率

    # 平均响应率 平均解决率 平均满意率
    if lvLianXiAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgLian).value = lvLianXiAverage[inset_bu_men]  # 平均响应率
    if lvJieJueAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgJie).value = lvJieJueAverage[inset_bu_men]  # 平均解决率
    if lvManYiFinalAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgMan).value = lvManYiFinalAverage[inset_bu_men]  # 平均满意率

    # 得分情况
    if deFenLianXi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colDeFenLian).value = deFenLianXi[inset_bu_men]  # 响应率得分
    if deFenJieJue[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colDeFenJie).value = deFenJieJue[inset_bu_men]  # 解决率得分
    if deFenManYi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colDeFenMan).value = deFenManYi[inset_bu_men]  # 满意率得分
    if deFenGong[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colDeFenGong).value = deFenGong[inset_bu_men]  # 工单里得分
    if deFenCai[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colDeFenCai).value = deFenCai[inset_bu_men]  # 材料报送得分
    sheet_insert.cell(rowInsert, colDeFen).value = de_fen  # 得分
    if is_she_qu == 1:
        sheet_insert.cell(rowInsert, colSuoShuPianQu).value = listSuoShuPianQu[inset_bu_men]  # 所属片区
        sheet_insert.cell(rowInsert, colFenDang).value = listFenDang[inset_bu_men]  # 分档
        if listQianRenSuQiu[inset_bu_men] != 0:
            sheet_insert.cell(rowInsert, colJieDanInfo).value = listQianRenSuQiu[inset_bu_men]  # 接单情况-千人诉求量



def make_pai_ming(sheet_make, list_make, is_she_qu=0):
    for indexName in range(0, len(list_make)):
        nameIndex = list_make[indexName]
        insert_into_sheet(sheet_make, nameIndex, deFenFinal[nameIndex], is_she_qu)


make_pai_ming(sheetKeShi, listKeShi)
make_pai_ming(sheetSheQu, listSheQu, 1)


def inset_he_ji_bei_zhu(sheet_he_ji, str_bei_zhu, is_she_qu=0):
    rowCount = sheet_he_ji.max_row
    colCount = sheet_he_ji.max_column
    numLast = 0
    colDeFenHeJi = get_col_index(sheet_he_ji, 2, '得分')
    colPaiMingHeJi = get_col_index(sheet_he_ji, 2, '排名')
    for rowHeJi in range(4, rowCount+1):
        sheet_he_ji.cell(rowHeJi, 1).value = rowHeJi-3
        if rowHeJi != 4:
            valueDeFen = sheet_he_ji.cell(rowHeJi, colDeFenHeJi).value
            if valueDeFen is not None:
                if valueDeFen == numLast:
                    sheet_he_ji.cell(rowHeJi, colPaiMingHeJi).value = sheet_he_ji.cell(rowHeJi-1, colPaiMingHeJi).value
                else:
                    sheet_he_ji.cell(rowHeJi, colPaiMingHeJi).value = sheet_he_ji.cell(rowHeJi, 1).value
                numLast = valueDeFen
        else:
            sheet_he_ji.cell(rowHeJi, colPaiMingHeJi).value = 1
            numLast = sheet_he_ji.cell(rowHeJi, colDeFenHeJi).value
    # get_column_letter(rowCount+1)
    strHeJi = 'A{}:B{}'.format(rowCount+1, rowCount+1)
    sheet_he_ji.merge_cells(range_string=strHeJi)
    sheet_he_ji.cell(rowCount+1, 1).value = '合计'
    if is_she_qu == 1:
        sheet_he_ji.merge_cells(range_string='A{}:AJ{}'.format(rowCount + 2, rowCount + 2))
        # strHeJi2 = 'A{}:AJ{}'.format(rowCount + 2, rowCount + 2)
    else:
        sheet_he_ji.merge_cells(range_string='A{}:AI{}'.format(rowCount + 2, rowCount + 2))
        # strHeJi2 = 'A{}:AI{}'.format(rowCount + 2, rowCount + 2)
    # sheet_he_ji.merge_cells(range_string=strHeJi2)
    sheet_he_ji.cell(rowCount + 2, 1).value = str_bei_zhu
    sheet_he_ji.row_dimensions[rowCount + 2].height = 50
    for colHeJi in range(3, colCount+1):
        valueColCell = sheet_he_ji.cell(3, colHeJi).value
        colAvai = 0
        if valueColCell is not None:
            if valueColCell.find('率') == -1:
                colAvai = colHeJi
        else:
            valueColCell2 = sheet_he_ji.cell(2, colHeJi).value
            if valueColCell2 is not None:
                if valueColCell2 == '考核总量' or valueColCell2 == '裸分得分' or valueColCell2 == '裸分失分':
                    colAvai = colHeJi
        if colAvai != 0:
            key = sheet_he_ji.cell(rowCount + 1, colAvai).coordinate
            # 求和的开始单元格地址
            start = sheet_he_ji.cell(4, colAvai).coordinate
            # 求和的结束单元格地址
            end = sheet_he_ji.cell(rowCount, colAvai).coordinate
            sheet_he_ji[key] = f'=SUM({start}:{end})'


strMain = '备注：' + strMain
inset_he_ji_bei_zhu(sheetKeShi, strMain)
inset_he_ji_bei_zhu(sheetSheQu, strMain)
# def inset_into_sheet_in_line(line)
bookKaoHe.save(fileResultSave)


